package org.anderground.app.content;

import org.anderground.app.R;
import org.anderground.app.model.AppModel;
import org.anderground.app.storage.db.DbStorage;
import org.anderground.app.storage.db.DbStorage.CitiesTable;
import org.anderground.app.storage.db.DbStorage.CityAliasesTable;
import org.anderground.app.storage.db.DbStorage.CountriesTable;
import org.anderground.app.storage.db.DbStorage.StationAliasesTable;
import org.anderground.app.storage.db.DbStorage.StationsTable;

import android.app.SearchManager;
import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.provider.BaseColumns;

/**
 * @author Eldar Abusalimov
 */
public class AndergroundProvider extends ContentProvider {

	public static final String AUTHORITY = "org.anderground.provider";

	/**
	 * Cities table.
	 */
	public static final class Cities implements BaseColumns {

		/**
		 * The path part of the {@link #CONTENT_URI}
		 */
		public static final String URI_PATH = "cities";

		/**
		 * The content:// style URL for this table
		 */
		public static final Uri CONTENT_URI = Uri.parse("content://"
				+ AUTHORITY + "/" + URI_PATH);

		/**
		 * The MIME type of {@link #CONTENT_URI} providing a directory of
		 * cities.
		 */
		public static final String CONTENT_TYPE_DIR = "vnd.android.cursor.dir/vnd.anderground.city";

		/**
		 * The MIME type of a {@link #CONTENT_URI} sub-directory of a single
		 * city.
		 */
		public static final String CONTENT_TYPE_ITEM = "vnd.android.cursor.item/vnd.anderground.city";

		/**
		 * The city's name
		 * <P>
		 * Type: TEXT
		 * </P>
		 */
		public static final String NAME = "name";

		/**
		 * The code of the city's country
		 * <P>
		 * Type: TEXT
		 * </P>
		 */
		public static final String COUNTRY_CODE = "country_code";
		
		/**
		 * The city's country
		 * <P>
		 * Type: TEXT
		 * </P>
		 */
		public static final String COUNTRY_NAME = "country_name";
		
		/**
		 * The default sort order for this table
		 */
		public static final String DEFAULT_SORT_ORDER = NAME + " ASC";

		/**
		 * The latitude
		 * <P>
		 * Type: INTEGER
		 * </P>
		 */
		public static final String LATITUDE = "latitude";

		/**
		 * The longitude
		 * <P>
		 * Type: INTEGER
		 * </P>
		 */
		public static final String LONGITUDE = "longitude";

		/**
		 * The private constructor.
		 */
		private Cities() {
		}

	}

	/**
	 * Stations table.
	 */
	public static final class Stations implements BaseColumns {

		/**
		 * The path part of the {@link #CONTENT_URI}
		 */
		public static final String URI_PATH = "stations";

		/**
		 * The content:// style URL for this table
		 */
		public static final Uri CONTENT_URI = Uri.parse("content://"
				+ AUTHORITY + "/" + URI_PATH);

		/**
		 * The MIME type of {@link #CONTENT_URI} providing a directory of
		 * stations.
		 */
		public static final String CONTENT_TYPE_DIR = "vnd.android.cursor.dir/vnd.anderground.station";

		/**
		 * The MIME type of a {@link #CONTENT_URI} sub-directory of a single
		 * station.
		 */
		public static final String CONTENT_TYPE_ITEM = "vnd.android.cursor.item/vnd.anderground.station";

		/**
		 * The station's name
		 * <P>
		 * Type: TEXT
		 * </P>
		 */
		public static final String NAME = "name";

		/**
		 * The default sort order for this table
		 */
		public static final String DEFAULT_SORT_ORDER = NAME + " ASC";

		/**
		 * The id of the city
		 * <P>
		 * Type: INTEGER (foreign key to cities table on the _ID field)
		 * </P>
		 */
		public static final String CITY_ID = "city_id";

		/**
		 * The latitude
		 * <P>
		 * Type: INTEGER
		 * </P>
		 */
		public static final String LATITUDE = "latitude";

		/**
		 * The longitude
		 * <P>
		 * Type: INTEGER
		 * </P>
		 */
		public static final String LONGITUDE = "longitude";

		/**
		 * The private constructor.
		 */
		private Stations() {
		}

	}

	/**
	 * Suggestions view table.
	 * 
	 * @author Eldar Abusalimov
	 */
	public static final class Suggestions implements BaseColumns {

		/**
		 * The suggestion itself
		 * <P>
		 * Type: TEXT
		 * </P>
		 */
		public static final String SUGGESTION = "suggestion";

		/**
		 * The default name
		 * <P>
		 * Type: TEXT
		 * </P>
		 */
		public static final String CANONICAL_NAME = "canonical_name";

		/**
		 * The primary line of text
		 * <P>
		 * Type: TEXT
		 * </P>
		 */
		public static final String TEXT_1 = SearchManager.SUGGEST_COLUMN_TEXT_1;

		/**
		 * A comment to the suggestion
		 * <P>
		 * Type: TEXT
		 * </P>
		 */
		public static final String TEXT_2 = SearchManager.SUGGEST_COLUMN_TEXT_2;

		/**
		 * The first icon
		 * <P>
		 * Type: INTEGER
		 * </P>
		 */
		public static final String ICON_1 = SearchManager.SUGGEST_COLUMN_ICON_1;

		/**
		 * The second icon
		 * <P>
		 * Type: INTEGER
		 * </P>
		 */
		public static final String ICON_2 = SearchManager.SUGGEST_COLUMN_ICON_2;

		/**
		 * The data that will be used when forming the suggestion's intent
		 * <P>
		 * Type: TEXT
		 * </P>
		 */
		public static final String INTENT_DATA = SearchManager.SUGGEST_COLUMN_INTENT_DATA;

		/**
		 * The data id
		 * <P>
		 * Type: INTEGER
		 * </P>
		 */
		public static final String INTENT_DATA_ID = SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID;

		/**
		 * Used for sorting the results
		 * <P>
		 * Type: INTEGER
		 * </P>
		 */
		public static final String RELEVANCE = "relevance";

		/**
		 * Used for filtering the results (selecting only the stations of the
		 * currently displayed city)
		 * <P>
		 * Type: INTEGER (city id for stations and 0 for cities)
		 * </P>
		 */
		public static final String CITY_ID = "city_id";

		/**
		 * The default sort order for this table
		 */
		public static final String DEFAULT_SORT_ORDER = RELEVANCE + " DESC, "
				+ TEXT_1 + " ASC";

		/**
		 * The private constructor.
		 */
		private Suggestions() {
		}

	}

	private static final int MATCH_CITIES = 1;
	private static final int MATCH_CITY_ID = 2;
	private static final int MATCH_STATIONS = 4;
	private static final int MATCH_STATION_ID = 5;
	private static final int MATCH_SUGGEST = 6;

	private static final String TABLE_CITIES = "v_cities";
	private static final String TABLE_STATIONS = "v_stations";
	private static final String TABLE_SUGGESTIONS = "v_suggestions";

	private static UriMatcher uriMatcher;
	static {
		uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);

		uriMatcher.addURI(AUTHORITY, Cities.URI_PATH, MATCH_CITIES);
		uriMatcher.addURI(AUTHORITY, Cities.URI_PATH + "/#", MATCH_CITY_ID);
		uriMatcher.addURI(AUTHORITY, Stations.URI_PATH, MATCH_STATIONS);
		uriMatcher
				.addURI(AUTHORITY, Stations.URI_PATH + "/#", MATCH_STATION_ID);
		uriMatcher.addURI(AUTHORITY, SearchManager.SUGGEST_URI_PATH_QUERY,
				MATCH_SUGGEST);
	}

	private DbStorage dbStorage;

	private static String fullFieldName(String table, String field) {
		return table + "." + field;
	}

	@Override
	public boolean onCreate() {
		dbStorage = DbStorage.getInstance(getContext());
		dropAndCreateViews(dbStorage.getWritableDatabase());
		return true;
	}

	private static void dropAndCreateViews(SQLiteDatabase db) {

		final String tCountries = CountriesTable.NAME;
		final String tCountries_Id = CountriesTable.Columns._ID;
		final String tCountries_Code = CountriesTable.Columns.CODE;
		final String tCountries_Name = CountriesTable.Columns.NAME;

		final String tCountries_Id_Full = fullFieldName(tCountries,
				tCountries_Id);
		final String tCountries_Code_Full = fullFieldName(tCountries,
				tCountries_Code);
		final String tCountries_Name_Full = fullFieldName(tCountries,
				tCountries_Name);

		final String tCities = CitiesTable.NAME;
		final String tCities_Id = CitiesTable.Columns._ID;
		final String tCities_MapId = CitiesTable.Columns.MAP_ID;
		final String tCities_CountryId = CitiesTable.Columns.COUNTRY_ID;
		final String tCities_Name = CitiesTable.Columns.NAME;
		final String tCities_Latitude = CitiesTable.Columns.LATITUDE;
		final String tCities_Longitude = CitiesTable.Columns.LONGITUDE;

		final String tCities_Id_Full = fullFieldName(tCities, tCities_Id);
		final String tCities_Name_Full = fullFieldName(tCities, tCities_Name);
		final String tCities_MapId_Full = fullFieldName(tCities, tCities_MapId);
		final String tCities_CountryId_Full = fullFieldName(tCities,
				tCities_CountryId);
		final String tCities_Latitude_Full = fullFieldName(tCities,
				tCities_Latitude);
		final String tCities_Longitude_Full = fullFieldName(tCities,
				tCities_Longitude);

		final String tStations = StationsTable.NAME;
		final String tStations_Id = StationsTable.Columns._ID;
		final String tStations_MapId = StationsTable.Columns.MAP_ID;
		final String tStations_Name = StationsTable.Columns.NAME;
		final String tStations_Latitude = StationsTable.Columns.LATITUDE;
		final String tStations_Longitude = StationsTable.Columns.LONGITUDE;

		final String tStations_Id_Full = fullFieldName(tStations, tStations_Id);
		final String tStations_MapId_Full = fullFieldName(tStations,
				tStations_MapId);
		final String tStations_Name_Full = fullFieldName(tStations,
				tStations_Name);
		final String tStations_Latitude_Full = fullFieldName(tStations,
				tStations_Latitude);
		final String tStations_Longitude_Full = fullFieldName(tStations,
				tStations_Longitude);

		final String tCityAliases = CityAliasesTable.NAME;
		final String tCityAliases_Name = CityAliasesTable.Columns.NAME;
		final String tCityAliases_CityId = CityAliasesTable.Columns.CITY_ID;

		final String tCityAliases_Name_Full = fullFieldName(tCityAliases,
				tCityAliases_Name);
		final String tCityAliases_CityId_Full = fullFieldName(tCityAliases,
				tCityAliases_CityId);

		final String tStationAliases = StationAliasesTable.NAME;
		final String tStationAliases_Name = StationAliasesTable.Columns.NAME;
		final String tStationAliases_StationId = StationAliasesTable.Columns.STATION_ID;

		final String tStationAliases_Name_Full = fullFieldName(tStationAliases,
				tStationAliases_Name);
		final String tStationAliases_StationId_Full = fullFieldName(
				tStationAliases, tStationAliases_StationId);

		final String vCities = TABLE_CITIES;
		final String vCities_Id = Cities._ID;
		final String vCities_Name = Cities.NAME;
		final String vCities_CountryCode = Cities.COUNTRY_CODE;
		final String vCities_CountryName = Cities.COUNTRY_NAME;
		final String vCities_Latitude = Cities.LATITUDE;
		final String vCities_Longitude = Cities.LONGITUDE;

		final String vStations = TABLE_STATIONS;
		final String vStations_Id = Stations._ID;
		final String vStations_CityId = Stations.CITY_ID;
		final String vStations_Name = Stations.NAME;
		final String vStations_Latitude = Stations.LATITUDE;
		final String vStations_Longitude = Stations.LONGITUDE;

		final String vSuggestions = TABLE_SUGGESTIONS;
		final String vSuggestions_Id = Suggestions._ID;
		final String vSuggestions_Text1 = Suggestions.TEXT_1;
		final String vSuggestions_Text2 = Suggestions.TEXT_2;
		final String vSuggestions_Suggestion = Suggestions.SUGGESTION;
		final String vSuggestions_CanonicalName = Suggestions.CANONICAL_NAME;
		final String vSuggestions_Icon1 = Suggestions.ICON_1;
		final String vSuggestions_Icon2 = Suggestions.ICON_2;
		final String vSuggestions_IntentData = Suggestions.INTENT_DATA;
		final String vSuggestions_IntentDataId = Suggestions.INTENT_DATA_ID;
		final String vSuggestions_Relevance = Suggestions.RELEVANCE;
		final String vSuggestions_CityId = Suggestions.CITY_ID;

		db.execSQL("DROP VIEW IF EXISTS " + vSuggestions);
		db.execSQL("DROP VIEW IF EXISTS " + vStations);
		db.execSQL("DROP VIEW IF EXISTS " + vCities);

		db.execSQL("CREATE VIEW " + vCities + " AS SELECT "
				+ tCities_Id_Full + " AS " + vCities_Id + ", "
				+ tCities_Latitude_Full + " AS " + vCities_Latitude + ", "
				+ tCities_Longitude_Full + " AS " + vCities_Longitude + ", "
				+ tCities_Name_Full + " AS " + vCities_Name + ", "
				+ tCountries_Code_Full + " AS " + vCities_CountryCode + ", "
				+ tCountries_Name_Full + " AS " + vCities_CountryName
			+ " FROM "
				+ tCities + " AS " + tCities
				+ " INNER JOIN " + tCountries + " AS " + tCountries
					+ " ON " + tCities_CountryId_Full + " = " + tCountries_Id_Full
		+ " ;");
		db.execSQL("CREATE VIEW " + vStations + " AS SELECT "
				+ tStations_Id_Full + " AS " + vStations_Id + ", "
				+ tStations_Latitude_Full + " AS " + vStations_Latitude + ", "
				+ tStations_Longitude_Full + " AS " + vStations_Longitude + ", "
				+ tCities_Id_Full + " AS " + vStations_CityId + ", "
				+ tStations_Name_Full + " AS " + vStations_Name
			+ " FROM "
				+ tStations + " AS " + tStations
				+ " INNER JOIN " + tCities + " AS " + tCities
					+ " ON " + tStations_MapId_Full + " = " + tCities_MapId_Full
		+ " ;");

		db.execSQL("CREATE VIEW " + vSuggestions + " AS "
			+ " SELECT DISTINCT "
				+ " 0 AS " + vSuggestions_Id + ", "
				+ " 4 AS " + vSuggestions_Relevance + ", "
				+ tCities_Id_Full + " AS " + vSuggestions_CityId + ", "
				+ tStations_Name_Full + " AS " + vSuggestions_Text1 + ", "
				+ tStations_Name_Full + " AS " + vSuggestions_Suggestion + ", "
				+ tStations_Name_Full + " AS " + vSuggestions_CanonicalName + ", "
				+ tCities_Name_Full + " AS " + vSuggestions_Text2 + ", "
				+ R.drawable.matmex_small + " AS " + vSuggestions_Icon1 + ", "
				+ R.drawable.matmex_small + "  AS " + vSuggestions_Icon2 + ", "
				+ "'" + Stations.CONTENT_URI + "' AS " + vSuggestions_IntentData + ", "
				+ tStations_Id_Full + " AS " + vSuggestions_IntentDataId
			+ " FROM "
				+ tStations + " AS " + tStations
				+ " INNER JOIN " + tCities + " AS "+ tCities
					+ " ON " + tStations_MapId_Full + " = " + tCities_MapId_Full
		+ " UNION ALL "	
			+ " SELECT DISTINCT "
				+ " 0, "
				+ " 2, "
				+ " 0, " // this is a city, so do not filter it
				+ tCities_Name_Full + ", "
				+ tCities_Name_Full + ", "
				+ tCities_Name_Full + ", "
				+ tCountries_Code_Full + " || ', ' || " + tCountries_Name_Full + ", "
				+ R.drawable.star_big_on + ", "
				+ R.drawable.star_big_on + ", "
				+ "'" + Cities.CONTENT_URI + "' , "
				+ tCities_Id_Full
			+ " FROM "
				+ tCities + " AS " + tCities
			+ " INNER JOIN " + tCountries + " AS " + tCountries
				+ " ON " + tCities_CountryId_Full + " = " + tCountries_Id_Full
		+ " UNION ALL "	
			 + " SELECT DISTINCT "
				+ " 0, "
				+ " 3, "
				+ tCities_Id_Full + " , "
				+ tStationAliases_Name_Full + ", "
				+ tStationAliases_Name_Full + ", "
				+ tStations_Name_Full + ", "
				+ " '(' || " + tStations_Name_Full + " || '), ' || "
					+ tCities_Name_Full + ", "
				+ R.drawable.matmex_small + ", "
				+ R.drawable.matmex_small + ", "
				+ "'" + Stations.CONTENT_URI + "' , "
				+ tStations_Id_Full
			+ " FROM "
				+ tStationAliases + " AS " + tStationAliases
				+ " LEFT JOIN " + tStations + " AS "+ tStations
					+ " ON " + tStationAliases_StationId_Full  + " = "+ tStations_Id_Full
				+ " INNER JOIN " + tCities + " AS "+ tCities
					+ " ON " + tStations_MapId_Full + " = " + tCities_MapId_Full
		+ " UNION ALL "	
			 + " SELECT DISTINCT "
				+ " 0, "
				+ " 1, "
				+ " 0, " // this is a city, so do not filter it
				+ tCityAliases_Name_Full + ", "
				+ tCityAliases_Name_Full + ", "
				+ tCities_Name_Full + ", "
				+ " '(' || " + tCities_Name_Full + " || '), ' || "
					+ tCountries_Code_Full + " || ', ' || " + tCountries_Name_Full + ", "
				+ R.drawable.star_big_on + ", "
				+ R.drawable.star_big_on + ", "
				+ "'" + Cities.CONTENT_URI + "' , "
				+ tCities_Id_Full
			+ " FROM "
				+ tCityAliases + " AS " + tCityAliases
				+ " INNER JOIN " + tCities + " AS "+ tCities
					+ " ON " + tCityAliases_CityId_Full  + " = "+ tCities_Id_Full
				+ " INNER JOIN " + tCountries + " AS " + tCountries
					+ " ON " + tCities_CountryId_Full + " = " + tCountries_Id_Full
		+ " ;");
	}

	@Override
	public Cursor query(Uri uri, String[] projection, String selection,
			String[] selectionArgs, String sortOrder) {

		SQLiteQueryBuilder qBuilder = new SQLiteQueryBuilder();

		switch (uriMatcher.match(uri)) {
		case MATCH_CITY_ID:
			qBuilder.appendWhere(Cities._ID + " = " + ContentUris.parseId(uri));
		case MATCH_CITIES:
			qBuilder.setTables(TABLE_CITIES);
			if (sortOrder == null || sortOrder.length() == 0) {
				sortOrder = Cities.DEFAULT_SORT_ORDER;
			}
			break;

		case MATCH_STATION_ID:
			qBuilder.appendWhere(Stations._ID + " = "
					+ ContentUris.parseId(uri));
		case MATCH_STATIONS:
			qBuilder.setTables(TABLE_STATIONS);
			if (sortOrder == null || sortOrder.length() == 0) {
				sortOrder = Stations.DEFAULT_SORT_ORDER;
			}
			break;

		case MATCH_SUGGEST:
			projection = null;

			sortOrder = Suggestions.DEFAULT_SORT_ORDER;

			qBuilder.setTables(TABLE_SUGGESTIONS);

			selectionArgs[0] = selectionArgs[0] + "%";
			
			final String vSuggestions_Suggestion = Suggestions.SUGGESTION;
			final String vSuggestions_CityId = Suggestions.CITY_ID;
			long loadedCityId = AppModel.getInstance().getCityId();
			selection = vSuggestions_Suggestion + " LIKE ? AND ("
					+ vSuggestions_CityId + " = " + loadedCityId + " OR "
					+ vSuggestions_CityId + " = 0)";
			break;

		default:
			return null;
		}

		SQLiteDatabase db = dbStorage.getReadableDatabase();
		Cursor cursor = qBuilder.query(db, projection, selection,
				selectionArgs, null, null, sortOrder);

		cursor.setNotificationUri(getContext().getContentResolver(), uri);
		return cursor;
	}

	@Override
	public Uri insert(Uri uri, ContentValues values) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int update(Uri uri, ContentValues values, String selection,
			String[] selectionArgs) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int delete(Uri uri, String selection, String[] selectionArgs) {
		// TODO Auto-generated method stub
		return 0;
	}
	
	@Override
	public String getType(Uri uri) {
		return matchType(uri);
	}

	public static String matchType(Uri uri) {
		switch (uriMatcher.match(uri)) {
		case MATCH_CITIES:
			return Cities.CONTENT_TYPE_DIR;

		case MATCH_CITY_ID:
			return Cities.CONTENT_TYPE_ITEM;

		case MATCH_STATIONS:
			return Stations.CONTENT_TYPE_DIR;

		case MATCH_STATION_ID:
			return Stations.CONTENT_TYPE_ITEM;

		default:
			return null;
		}
	}

}
